package org.example.Dao;

import org.example.entity.Person;
import org.example.utils.DruidUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class PersonDao {


    /**
     * 通过邮箱密码查找用户信息
     *
     * @param email    邮箱
     * @param password 密码
     * @return 用户
     */
    public Person login(String email, String password) throws Exception {

        Connection connection = DruidUtil.getConnection();
        String sql = "select * from person where email = ? and password = ?";
        Person person = new Person();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, email);
        preparedStatement.setString(2, password);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            assembleUserInformation(resultSet, person);
            connection.close();
            preparedStatement.close();
            resultSet.close();
            return person;
        }
        return null;
    }


    /**
     * 判断邮箱是否存在
     *
     * @param email 注册邮箱
     * @return 未注册为false，注册过的为true
     */
    public boolean isEmailExist(String email) throws Exception {
        Connection connection = DruidUtil.getConnection();
        String sql = "select * from person where email = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, email);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            connection.close();
            preparedStatement.close();
            resultSet.close();
            return true;
        }
        return false;
    }

    /**
     * 注册用户
     *
     * @param username 用户名
     * @param password 密码
     * @param email    邮箱
     * @return 注册成功为true，注册失败为false
     */
    public boolean register(String username, String password, String email) throws Exception {
        Connection connection = DruidUtil.getConnection();
        String sql = "insert into person(username,email,password) values(?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, email);
        preparedStatement.setString(3, password);
        int status = preparedStatement.executeUpdate();
        connection.close();
        preparedStatement.close();
        return status > 0;
    }

    /**
     * 获取所有普通用户的信息
     *
     * @return 普通用户集合
     */
    public List<Person> getNormalPersonList() throws Exception {
        Connection connection = DruidUtil.getConnection();
        String sql = "select * from person where level = 0";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<Person> personList = new ArrayList<>();
        while (resultSet.next()) {
            Person person = new Person();
            assembleUserInformation(resultSet, person);
            personList.add(person);
        }
        connection.close();
        preparedStatement.close();
        resultSet.close();
        return personList;
    }


    /**
     * 组装用户信息
     *
     * @param resultSet 结果集
     * @param person    用户
     * @throws SQLException 异常
     */
    private void assembleUserInformation(ResultSet resultSet, Person person) throws SQLException {
        person.setPid(resultSet.getInt("pid"));
        person.setEmail(resultSet.getString("email"));
        person.setPassword(resultSet.getString("password"));
        person.setUsername(resultSet.getString("username"));
        person.setLevel(resultSet.getInt("level"));
        person.setDate(resultSet.getDate("date"));
    }

    /**
     * 通过人员id删除
     *
     * @param pid 人员id
     * @return 成功行数
     */
    public int delPersonByPid(int pid) throws Exception {

        Connection connection = DruidUtil.getConnection();
        String sql = "delete from person where pid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, pid);
        int count = preparedStatement.executeUpdate();
        preparedStatement.close();
        connection.close();
        return count;
    }

    public Person getPersonByPid(int pid) throws Exception {
        Connection connection = DruidUtil.getConnection();

        String sql = "select * from  person where pid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, pid);
        ResultSet resultSet = preparedStatement.executeQuery();
        Person person = null;
        if (resultSet.next()) {
            person = new Person();
            assembleUserInformation(resultSet, person);

            resultSet.close();
        }
        connection.close();
        preparedStatement.close();
        return person;
    }

    /**
     * 更新用户信息
     *
     * @param pid      用户id
     * @param username 用户名
     * @param password 用户密码
     */
    public int updatePerson(int pid, String username, String password) throws Exception {
        Connection connection = DruidUtil.getConnection();
        String sql = "update person set username = ?,password = ? where pid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, password);
        preparedStatement.setInt(3, pid);
        int count = preparedStatement.executeUpdate();
        preparedStatement.close();
        connection.close();
        return count;
    }


    /**
     * 通过条件查询信息
     *
     * @param pid
     * @param username
     * @param email
     * @param beginDate
     * @param endDate
     * @return
     */
    public List<Person> findPerson(int pid, String username, String email, Date beginDate, Date endDate) throws Exception {

        List<Person> personList = new ArrayList<>();
        String sql = "SELECT * FROM person WHERE 1=1 and level = 0";
        List<Object> params = new ArrayList<>();

        Connection connection = DruidUtil.getConnection();
        StringBuilder sb = new StringBuilder(sql);

        if (pid != -1) {
            sb.append(" AND pid = ?");
            params.add(pid);
        }

        if (username != null && !username.isEmpty()) {
            sb.append(" AND username LIKE ?");
            params.add("%" + username + "%");
        }

        if (beginDate != null) {
            sb.append(" AND date >= ?");
            params.add(beginDate);
        }

        if (endDate != null) {
            sb.append(" AND date <= ?");
            params.add(endDate);
        }

        if (!email.isEmpty()) {
            sb.append(" AND email = ?");
            params.add(email);
        }

        sql = sb.toString();
        System.out.println("find person list sql ==> " + sql);

        try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(i + 1, params.get(i));
            }

            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                while (resultSet.next()) {
                    Person person = new Person();
                    assembleUserInformation(resultSet, person);
                    personList.add(person);
                }
            }
            preparedStatement.close();
            connection.close();
        }

        return personList;
    }

    /**
     * 更新密码
     *
     * @param email    邮箱
     * @param password 密码
     * @return
     */
    public int updatePwd(String email, String password) throws Exception {

        Connection connection = DruidUtil.getConnection();
        String sql = "update person set password = ? where email = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, password);
        preparedStatement.setString(2, email);
        int count = preparedStatement.executeUpdate();
        preparedStatement.close();
        connection.close();
        return count;
    }
}
